package com.sam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class JDBCSQLProg {

	public static void main(String[] args) {
	// JDBC API Objects Declaration
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs1 = null, rs2 = null;
	PreparedStatement preStmt = null;
			
	// Username and Password for login the database
	String strUser = "sa";
	String strPass = "111";
			
	// Register the Driver
	try
		{
		// Another way is "new sun.jdbc.odbc.JdbcOdbcDriver()"
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			}	
			catch (ClassNotFoundException e)
			{
				// Exception handler for Class.forName(...)
				System.err.println("Error! " + e.getMessage());
				System.err.println("Please install JDBC-ODBC Driver first.");
				return;
			}
			
			// Main Process of retrieving data with JDBC
			try
			{
				// Create Connection
				conn = 
				DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;database=CH06範例資料庫", strUser, strPass);
				conn.setAutoCommit(false);				
				
				// Create SQL Statement
				String strSQL = "SELECT * FROM 員工";
				stmt = conn.createStatement();
				
				// Return the Results
				rs1 = stmt.executeQuery(strSQL);
				// Retrieve Data
				while (rs1.next())
				{
					System.out.println(rs1.getString("姓名"));
				}
				System.out.println("==========");
				
				//使用Stored Procedure
				
				preStmt=conn.prepareStatement("{call spQueryProd (default)}");
				rs2=preStmt.executeQuery();
				while (rs2.next()){
					System.out.println(rs2.getString("產品名稱"));
					System.out.println(rs2.getString("備註"));				
				}
				
			}
			catch (SQLException e)
			{
				System.err.println("SQL Error! " + e.getMessage());
				e.printStackTrace(System.err);
			}
			finally
			{
				// Close all resource on server and release memory at client
				try
				{
					conn.setAutoCommit(true);
					rs1.close();
					rs2.close();
					stmt.close();
					conn.close();
				}
				catch (SQLException e)
				{
					System.err.println("SQL Error! " + e.getMessage());
					e.printStackTrace(System.err);
				}
				rs1 = null;
				rs2 = null;
				stmt = null;
				conn = null;
			}
		}

	}
	
